package com.web.test.dao;

import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import com.web.test.bean.Message;
import com.web.test.utils.JDBCUtil;
import com.web.test.utils.SQLUtils;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class MessageDao {

    // execute()返回boolean值，如果是查询返回true，如果是更新false
    public boolean insert(Message message) throws ClassNotFoundException,
            SQLException, IOException {

        Connection connection = JDBCUtil.getConnection();
        String sql = "insert into message values(?,?,?,?,?,?)";
        PreparedStatement pStatement = connection.prepareStatement(sql);

        pStatement.setString(1, "");
        pStatement.setString(2, message.getName());
        pStatement.setInt(3, message.getAge());
        pStatement.setString(4, message.getSex());
        pStatement.setString(5, message.getPhone());
        pStatement.setString(6, message.getLoc());

        if (!pStatement.execute()) {
            JDBCUtil.close(connection, pStatement, null);
            return true;
        }

        return false;

    }

    public List<Message> findAndSort(String sql)
            throws ClassNotFoundException, SQLException, IOException {

        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        ResultSet rSet = statement.executeQuery(sql);

        List<Message> msList = getMessagesByRS(rSet);

        JDBCUtil.close(connection, statement, rSet);

        return msList;

    }

    public boolean updateAndDelete(String sql)
            throws ClassNotFoundException, SQLException, IOException {

        boolean flag = false;

        Connection connection = JDBCUtil.getConnection();

        Statement statement = connection.createStatement();

        if (statement.executeUpdate(sql) > 0) {
            JDBCUtil.close(connection, statement, null);
            flag = true;
        }
        return flag;

    }

    public List<Message> page(int countPage, int whichPage)
            throws ClassNotFoundException, SQLException, IOException {
        Connection connection = JDBCUtil.getConnection();
        String sql = SQLUtils.all;
        Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = statement.executeQuery(sql);


        rs.last();
        int totalCount = rs.getRow();

        int start = (whichPage - 1) * countPage + 1;
        int end = (totalCount <= start + countPage ? totalCount : start
                + countPage);

        rs.absolute(start);// 将游标移动到当前页起始位置
        statement.setMaxRows(end);// 设置最大记录数为为当前页的下标
        List<Message> msList = new ArrayList<Message>();

        for (int i = 0; i < countPage; i++) {

            if (rs.isAfterLast()){

                break;

            }else {

                Message message = new Message(rs.getInt("id"), rs.getString("name"),
                        rs.getInt("age"), rs.getString("sex"), rs.getString("phone"), rs.getString("address"));
                msList.add(message);
                rs.next();


            }
        }

        JDBCUtil.close(connection, statement, rs);


        return msList;
    }


    private List<Message> getMessagesByRS(ResultSet rSet) throws SQLException {

        List<Message> msList = new ArrayList<Message>();

         while (rSet.next()){

            int id = rSet.getInt("id");
            String nameM = rSet.getString("name");
            int age = rSet.getInt("age");
            String sex = rSet.getString("sex");
            String phone = rSet.getString("phone");
            String loc = rSet.getString("address");

            Message message = new Message(id, nameM, age, sex, phone, loc);
            msList.add(message);
        }
        return msList;
    }


}
